﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace KuPeiXun.Utility
{
    /// <summary>
    /// sqlserver数据访问
    /// </summary>
    /// ===============================================================
    /// 作者：邹学良 
    /// 时间：2013-03-21
    /// 描述：创建
    /// 历史：无
    /// ===============================================================
    public class SqlDbHepler
    {

        #region 数据库连接串  属性

        /// <summary>
        /// 数据库连接串
        /// </summary>
        private static string _sqlConnectionStr = string.Empty;

        /// <summary>
        /// 数据库连接串  属性
        /// </summary>
        public static string SqlConnectionStr
        {
            get { return _sqlConnectionStr; }
            set { _sqlConnectionStr = value; }
        }

        #endregion

        #region 通过sql语句获取数据
        /// <summary>
        /// 通过sql语句获取数据
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="table">返回DataSet中的表的名字</param>
        /// <param name="parameters">sql语句的参数</param>
        /// <returns></returns>
        public static DataSet GetDataBySql(string sqlStr, string table, params SqlParameter[] parameters)
        {
            DataSet ds = new DataSet(); ;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand com = new SqlCommand();
            try
            {
                com.Connection = conn;
                com.CommandText = sqlStr;
                com.CommandType = CommandType.Text;
                if (parameters != null && parameters.Length > 0)
                {
                    com.Parameters.AddRange(parameters);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                conn.Open();
                adapter.Fill(ds, table);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                com.Parameters.Clear();
            }
            return ds;
        }
        #endregion

        #region 通过存储过程语句获取数据
        /// <summary>
        /// 通过存储过程语句获取数据
        /// </summary>
        /// <param name="proc">存储过程名</param>
        /// <param name="table">返回DataSet中的表的名字</param>
        /// <param name="parameters">存储过程语句的参数</param>
        /// <returns></returns>
        public static DataSet GetDataByProc(string proc, string table, params SqlParameter[] parameters)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand com = new SqlCommand();
            try
            {
                com.Connection = conn;
                com.CommandText = proc;
                com.CommandType = CommandType.StoredProcedure;
                if (parameters != null && parameters.Length > 0)
                {
                    com.Parameters.AddRange(parameters);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                conn.Open();
                adapter.Fill(ds, table);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                com.Parameters.Clear();
            }
            return ds;
        }
        #endregion

        #region 通过sql语句执行非查询的行为返回影响行
        /// <summary>
        /// 通过sql语句执行非查询的行为返回影响行
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        public static int ExcuteNoneQueryBySql(string sqlStr, params SqlParameter[] parameters)
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlTransaction tran = null;
            SqlCommand com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = sqlStr;
            com.CommandType = CommandType.Text;
            if (parameters != null && parameters.Length > 0)
            {
                com.Parameters.AddRange(parameters);
            }
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com.Transaction = tran;
                result = com.ExecuteNonQuery();

            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }
            finally
            {
                tran.Commit();
                com.Parameters.Clear();
                conn.Close();
            }
            return result;
        }
        #endregion

        #region 通过存储过程执行非查询的行为返回影响行
        /// <summary>
        /// 通过存储过程执行非查询的行为返回影响行
        /// </summary>
        /// <param name="proc">存储过程</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        public static int ExcuteNoneQueryByProc(string proc, params SqlParameter[] parameters)
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlTransaction tran = null;
            SqlCommand com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = proc;
            com.CommandType = CommandType.StoredProcedure;
            if (parameters != null && parameters.Length > 0)
            {
                com.Parameters.AddRange(parameters);
            }
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com.Transaction = tran;
                result = com.ExecuteNonQuery();

            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }
            finally
            {
                tran.Commit();
                com.Parameters.Clear();
                conn.Close();
            }
            return result;
        }
        #endregion

        #region 执行一个sql语句返回一个结果
        /// <summary>
        /// 执行一个sql语句返回一个结果
        /// </summary>
        /// <param name="sqlstr">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static object ExcuteBySql(string sqlstr, params SqlParameter[] parameters)
        {
            object result = null;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand com = new SqlCommand();
            SqlTransaction tran = null;
            com.Connection = conn;
            com.CommandText = sqlstr;
            com.CommandType = CommandType.Text;
            if (parameters != null && parameters.Length > 0)
            {
                com.Parameters.AddRange(parameters);
            }
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com.Transaction = tran;
                result = com.ExecuteScalar();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                if (tran != null)
                {
                    tran.Commit();
                }
                com.Parameters.Clear();
                conn.Close();
            }
            return result;
        }
        #endregion

        #region 执行一个存储过程返回一个结果
        /// <summary>
        /// 执行一个存储过程返回一个结果
        /// </summary>
        /// <param name="proc">存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static object ExcuteByProc(string proc, params SqlParameter[] parameters)
        {
            object result = null;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand com = new SqlCommand();
            SqlTransaction tran = null;
            com.Connection = conn;
            com.CommandText = proc;
            com.CommandType = CommandType.StoredProcedure;
            if (parameters != null && parameters.Length > 0)
            {
                com.Parameters.AddRange(parameters);
            }
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com.Transaction = tran;
                result = com.ExecuteScalar();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                if (tran != null)
                {
                    tran.Commit();
                }
                com.Parameters.Clear();
                conn.Close();
            }
            return result;
        }
        #endregion

        #region 执行一个sql语句获取一个DataReader
        /// <summary>
        ///  执行一个sql语句获取一个DataReader
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static SqlDataReader ExcuteReaderBySql(string sqlStr, params SqlParameter[] parameters)
        {
            SqlDataReader reader = null;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = sqlStr;
            com.CommandType = CommandType.Text;
            if (parameters != null && parameters.Length > 0)
            {
                com.Parameters.AddRange(parameters);
            }
            SqlTransaction tran = null;
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com.Transaction = tran;
                reader = com.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                tran.Commit();
                com.Parameters.Clear();
                conn.Close();
            }
            return reader;
        }
        #endregion

        #region 执行一个存储过程获取一个DataReader
        /// <summary>
        ///  执行一个存储过程获取一个DataReader
        /// </summary>
        /// <param name="proc">存储过程 </param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static SqlDataReader ExcuteReaderByProc(string proc, params SqlParameter[] parameters)
        {
            SqlDataReader reader = null;
            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = proc;
            com.CommandType = CommandType.StoredProcedure;
            if (parameters != null && parameters.Length > 0)
            {
                com.Parameters.AddRange(parameters);
            }
            SqlTransaction tran = null;
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                com.Transaction = tran;
                reader = com.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                tran.Commit();
                com.Parameters.Clear();
                conn.Close();
            }
            return reader;
        }
        #endregion

        #region 执行一个存储过程等到一个返回值
        /// <summary>
        /// 执行一个存储过程等到一个返回值
        /// </summary>
        /// <param name="proc">存储过程名</param>
        /// <param name="paraOut">返回值得参数</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        public static object RunProcdureGetReturnValue(string proc, SqlParameter paraOut, params SqlParameter[] parameters)
        {
            object result = null;

            SqlConnection conn = new SqlConnection(_sqlConnectionStr);
            SqlCommand cmd = new SqlCommand();
            SqlTransaction tran = null;
            cmd.Connection = conn;
            cmd.CommandText = proc;
            cmd.CommandType = CommandType.StoredProcedure;
            paraOut.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(paraOut);
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                result = paraOut.Value;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                tran.Commit();
                cmd.Parameters.Clear();
                conn.Close();
            }

            return result;
        }
        #endregion
    }
}



